#include "dbadapter.h"
#include "iostream"

DBAdapter::DBAdapter(QObject *parent) :
QObject(parent)
{
	_db="testdb";
}

DBAdapter::DBAdapter(QString dbName, QObject *parent):QObject(parent)
{
	_db=dbName;
}

bool DBAdapter::InitDb()
{
	dbhelper conn;
	conn.GetConnection(_db);
	bool ret = conn.InitDataBase();
	return ret;
}

////基本方法
int DBAdapter::Insert(QString table,QVariantMap map)
{
	//	qDebug() << "insert map : " << map;
	dbhelper conn;
	conn.GetConnection(_db);
	return conn.Insert(table,map);
}

bool DBAdapter::Update(QString table, int fid, QVariantMap map)
{
	dbhelper conn;
	conn.GetConnection(_db);
	return conn.Update(table,map,"Fid="+QString::number(fid));
}

QVariantList DBAdapter::Select(QString table, QVariantMap map, QStringList columns, QString filter)
{
	dbhelper conn;
	conn.GetConnection(_db);
	return mapToVariant(conn.SimpleSelect(table,columns,map,filter));
}

bool DBAdapter::Delete(QString table, QString filter)
{
	dbhelper conn;
	conn.GetConnection(_db);
	return conn.Delete(table,filter);
}

QVariantMap DBAdapter::test()
{

	QVariantMap umap;
	umap.insert("Fname","jiahui");
	//	QList<QVariantMap> list;
	//	list.append(umap);
	//	qDebug() << "test DBAdapter : " << list;
	return umap;
}

//获取用户余额
//QVariantList DBAdapter::GetUserAsset(int UserId)
qreal DBAdapter::GetUserAsset()
{
	dbhelper conn;
	conn.GetConnection(_db);
//	QString strSql =
//			"select t1.fuserid,t1.famount+t2.fsum-t3.fsum as fassets from "
//			"(select fuserid,famount from t_bas_initialassets where fuserid = :id1) as t1,"
//			"(select sum(famount) as fsum from t_bas_income where fuserid = :id2) as t2,"
//			"(select sum(famount) as fsum from t_bas_payment where fuserid = :id3) as t3";
//	QVariantMap map;
//	map.insert(":id1",UserId);
//	map.insert(":id2",UserId);
//	map.insert(":id3",UserId);
//	return mapToVariant(conn.CustomSelect(strSql,map));
	QString strSql =
			"select famount from t_bas_initialassets";
	QVariantMap map;
	QList<QVariantMap> ret1 = conn.CustomSelect(strSql,map);
//	qDebug () << "ret1 = conn.CustomSelect(strSql,map);   " << ret1 ;
//	qreal assets = ret1[0]["famount"].toReal();
	double assets;
	if(ret1.count() == 0) assets=0;
	else assets = ret1[0]["famount"].toReal();
	return assets - GetPaymentSum() + GetIncomeSum();
}


///列表数据---收入列表，支出列表,收入类型表，支出类型表
QVariantList DBAdapter::GetAllIncome(QDateTime fromDate, QDateTime toDate, int UserId)
{
	dbhelper conn;
	conn.GetConnection(_db);
	QString strSql =
			"SELECT "
			"A.FID,A.FNUMBER,A.FAMOUNT,FITYPEID,B.FNAME AS FTYPENAME,FDATE,FYEAR,FMONTH,FDAY,A.FDESCRIPTION "
			"FROM "
			"T_BAS_INCOME A INNER JOIN T_BAS_INCOMETYPE B ON A.FITYPEID = B.FID ";
	QString timeFilter = ToolHelper::GetDateFilter(fromDate.date(),toDate.date());
	strSql = strSql + timeFilter + " AND A.FUSERID="+QString::number(UserId);
	std::cout<<strSql.toStdString()<<std::endl;
	//    qDebug() << "strSql : " << strSql;
	return mapToVariant(conn.CustomSelect(strSql,QVariantMap()));
}

//QList<QVariantMap> DBAdapter::GetAllPayment(QDateTime fromDate, QDateTime toDate, int UserId)
QVariantList DBAdapter::GetAllPayment(QDateTime fromDate, QDateTime toDate, int UserId)
{
	dbhelper conn;
	conn.GetConnection(_db);
	QString strSql =
			"SELECT "
			"A.FID,A.FNUMBER,A.FAMOUNT,FPTYPEID,B.FNAME AS FTYPENAME,FDATE,FYEAR,FMONTH,FDAY,A.FDESCRIPTION "
			"FROM "
			"T_BAS_PAYMENT A INNER JOIN T_BAS_PAYMENTTYPE B ON A.FPTYPEID = B.FID ";
	QString timeFilter = ToolHelper::GetDateFilter(fromDate.date(),toDate.date());
	strSql = strSql + timeFilter + " AND A.FUSERID="+QString::number(UserId);
	//	qDebug() << "strSql : " << strSql;
	//	qDebug() << "conn.CustomSelect : " << conn.CustomSelect(strSql,QVariantMap());
	//    return conn.CustomSelect(strSql,QVariantMap());
	//	QVariantMap umap;
	//	umap.insert("Fname","222");
	//	QList<QVariantMap> list;
	//	list.append(umap);
	//	QVariantMap variant(conn.CustomSelect(strSql,QVariantMap()));
	//	QVariantList list;
	//	list.append(QVariant(umap));
	//	list.append(QVariant(umap));
	return mapToVariant(conn.CustomSelect(strSql,QVariantMap()));
	//    return new QList() ;
}

QVariantList DBAdapter::GetIncomeType()
{
	dbhelper conn;
	QList<QVariantMap> ret;
	conn.GetConnection(_db);
	ret = conn.SimpleSelect("T_BAS_INCOMETYPE",QStringList("*"),QVariantMap(),QString());
	return mapToVariant(ret);
}

QVariantList DBAdapter::GetPaymentType()
{
	dbhelper conn;
	conn.GetConnection(_db);
	return mapToVariant(conn.SimpleSelect("T_BAS_PAYMENTTYPE",QStringList("*"),QVariantMap(),QString()));
}

///复杂报表

//饼图报表，按照收入/支出类型获取每个类型的 总和，全部总和，占有百分比
QVariantList DBAdapter::GetIncomeRptGroupByType(QDateTime fromDate,QDateTime toDate,int UserId)
{
	dbhelper conn;
	conn.GetConnection(_db);
	return mapToVariant(bizQueryHelper::GetIncomeRptGroupByType(conn,fromDate.date(),toDate.date(),UserId));
}

QVariantList DBAdapter::GetPaymentRptGroupByType(QDateTime fromDate,QDateTime toDate,int UserId)
{
	dbhelper conn;
	conn.GetConnection(_db);
	return mapToVariant(bizQueryHelper::GetPaymentRptGroupByType(conn,fromDate.date(),toDate.date(),UserId));
}

//时间段内收入/支出报表
QVariantList DBAdapter::GetIncomeRpt(QDateTime fromDate,QDateTime toDate,int UserId,QString filter)
{
	dbhelper conn;
	conn.GetConnection(_db);
	QStringList filters;
	filters.append(ToolHelper::GetDateFilter(fromDate.date(),toDate.date()));
	filters.append(" FUserId="+QString::number(UserId));
	if(!filter.isEmpty()) filters.append(filter);
	return mapToVariant(bizQueryHelper::GetIncomeRpt(conn,filters.join(" And ") + " group by FYEAR,FMONTH"));
}

QVariantList DBAdapter::GetPaymentRpt(QDateTime fromDate,QDateTime toDate,int UserId,QString filter)
{
	dbhelper conn;
	conn.GetConnection(_db);
	QStringList filters;
	filters.append(ToolHelper::GetDateFilter(fromDate.date(),toDate.date()));
	filters.append(" FUserId="+QString::number(UserId));
	if(!filter.isEmpty()) filters.append(filter);
	return mapToVariant(bizQueryHelper::GetPaymentRpt(conn,filters.join(" And ") + " group by FYEAR,FMONTH"));
}

QVariantList DBAdapter::GetdayPaymentRpt(QDateTime d,int UserId,QString filter)
{
	dbhelper conn;
	conn.GetConnection(_db);
	QStringList filters;
	QString year = QString::number(d.date().year());
	QString month = QString::number(d.date().month());
	filters.append(" Where FYear="+year);
	filters.append(" FMonth="+month);
	filters.append(" FUserId="+QString::number(UserId));
	if(!filter.isEmpty()) filters.append(filter);
	QString strSql = "SELECT SUM(FAMOUNT) as FSUM,FYEAR,FMONTH,FDAY FROM T_BAS_PAYMENT" + filters.join(" And ") + " group by FDAY";
	qDebug() << "strSql : " << strSql;
	return mapToVariant(conn.CustomSelect(strSql,QMap<QString,QVariant>()));
}

QVariantList DBAdapter::mapToVariant(QList<QVariantMap> maplist)
{
	QVariantList list;
	foreach(QVariantMap i , maplist)
	{
		QVariant va(i);
		list.append(va);
	}
	return list;
}

QDateTime DBAdapter::GetLastDate()
{
    dbhelper conn;
    conn.GetConnection(_db);
    QString strSql = "select max(fdate) as fmax from (select fdate from t_bas_payment"
            " union all"
            " select fdate from t_bas_income) as t1 ";
    QList<QVariantMap> ret = conn.CustomSelect(strSql,QMap<QString,QVariant>());
    if(ret.count()>0)
    {
        int v = ret[0]["fmax"].toInt();
        int year = v/10000;
        int month = v%10000/100;
        int day = v%100;
        QDate d(year,month,day);
        return QDateTime(d);
    }
    else
        return QDateTime(QDate(1900,0,0));
}

int DBAdapter::GetPaymentCount()
{
    dbhelper conn;
    conn.GetConnection(_db);
    QString strSql = "SELECT COUNT(FID) FCOUNT FROM T_BAS_PAYMENT";
    QList<QVariantMap> ret = conn.CustomSelect(strSql,QMap<QString,QVariant>());
//    return ret[0]["FCOUNT"].toInt();
    if(ret.count() != 0)
    	return ret[0]["FCOUNT"].toInt();
    else
    	return 0;
}

int DBAdapter::GetIncomeCount()
{
    dbhelper conn;
    conn.GetConnection(_db);
    QString strSql = "SELECT COUNT(FID) FCOUNT FROM T_BAS_INCOME";
    QList<QVariantMap> ret = conn.CustomSelect(strSql,QMap<QString,QVariant>());
//    return ret[0]["FCOUNT"].toInt();
    if(ret.count() != 0)
    	return ret[0]["FCOUNT"].toInt();
    else
    	return 0;
}

qreal DBAdapter::GetIncomeSum()
{
    dbhelper conn;
    conn.GetConnection(_db);
    QString strSql = "SELECT SUM(FAMOUNT) FSUM FROM T_BAS_INCOME";
    QList<QVariantMap> ret = conn.CustomSelect(strSql,QMap<QString,QVariant>());
//    return ret[0]["FSUM"].toReal();
    if(ret.count() != 0)
    	return ret[0]["FSUM"].toReal();
    else
    	return 0;
}

qreal DBAdapter::GetPaymentSum()
{
    dbhelper conn;
    conn.GetConnection(_db);
    QString strSql = "SELECT SUM(FAMOUNT) FSUM FROM T_BAS_PAYMENT";
    QList<QVariantMap> ret = conn.CustomSelect(strSql,QMap<QString,QVariant>());
//    return ret[0]["FSUM"].toReal();
    if(ret.count() != 0)
    	return ret[0]["FSUM"].toReal();
    else
    	return 0;
}
